using System;
using System.Text;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using OVS.BusinessObjects;

namespace OVS.DAL
{
	public partial class ScheduleDAO
	{
		public ScheduleDAO()
		{
			DbProviderHelper.GetConnection();
		}
		public List<Schedule> GetSchedules()
		{
			try
			{
				List<Schedule> lstSchedules = new List<Schedule>();
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECT [sche_id],[user_id] FROM [Schedule]",CommandType.Text);
				DbDataReader oDbDataReader = DbProviderHelper.ExecuteReader(oDbCommand);
				while (oDbDataReader.Read())
				{
					Schedule oSchedule = new Schedule();
					oSchedule.sche_id = Convert.ToInt32(oDbDataReader["sche_id"]);
					oSchedule.user_id = Convert.ToInt32(oDbDataReader["user_id"]);
					lstSchedules.Add(oSchedule);
				}
				oDbDataReader.Close();
				return lstSchedules;
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public Schedule GetSchedule(int sche_id)
		{
			try
			{
				Schedule oSchedule = new Schedule();
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECT [sche_id],[user_id] FROM [Schedule] WHERE [sche_id]=@sche_id",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@sche_id",DbType.Int32,sche_id));
				DbDataReader oDbDataReader = DbProviderHelper.ExecuteReader(oDbCommand);
				while (oDbDataReader.Read())
				{
					oSchedule.sche_id = Convert.ToInt32(oDbDataReader["sche_id"]);
					oSchedule.user_id = Convert.ToInt32(oDbDataReader["user_id"]);
				}
				oDbDataReader.Close();
				return oSchedule;
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int AddSchedule(Schedule oSchedule)
		{
			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("INSERT INTO [Schedule]([user_id])VALUES(@user_id)",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_id",DbType.Int32,oSchedule.user_id));

				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int UpdateSchedule(Schedule oSchedule)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("UPDATE [Schedule] SET [user_id]=@user_id WHERE [sche_id]=@sche_id",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_id",DbType.Int32,oSchedule.user_id));
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@sche_id",DbType.Int32,oSchedule.sche_id));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int RemoveSchedule(Schedule oSchedule)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("DELETE FROM [Schedule] WHERE [sche_id]=@sche_id",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@sche_id",DbType.Int32,oSchedule.sche_id));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int RemoveSchedule(int sche_id)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("DELETE FROM [Schedule] WHERE [sche_id]=@sche_id",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@sche_id",DbType.Int32,sche_id));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public List<Schedule> GetSchedulesOfUser(int user_id)
		{
			try
			{
				List<Schedule> lstSchedules = new List<Schedule>();
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECT [sche_id],[user_id] FROM [Schedule] WHERE [user_id]=@user_id",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_id",DbType.Int32,user_id));
				DbDataReader oDbDataReader = DbProviderHelper.ExecuteReader(oDbCommand);
				while (oDbDataReader.Read())
				{
					Schedule oSchedule = new Schedule();
					oSchedule.sche_id = Convert.ToInt32(oDbDataReader["sche_id"]);
					oSchedule.user_id = Convert.ToInt32(oDbDataReader["user_id"]);
					lstSchedules.Add(oSchedule);
				}
				oDbDataReader.Close();
				return lstSchedules;
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public User GetUserOfSchedule(int user_id)
		{
			try
			{
				User oUser = new User();
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECT [user_id],[user_name],[user_tel],[user_mail],[user_role],[user_lvl_notif],[user_pwd],[user_login] FROM [User] WHERE [user_id]=@user_id",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_id",DbType.Int32,user_id));
				DbDataReader oDbDataReader = DbProviderHelper.ExecuteReader(oDbCommand);
				while (oDbDataReader.Read())
				{
					oUser.user_id = Convert.ToInt32(oDbDataReader["user_id"]);
					oUser.user_name = Convert.ToString(oDbDataReader["user_name"]);
					oUser.user_tel = Convert.ToString(oDbDataReader["user_tel"]);
					oUser.user_mail = Convert.ToString(oDbDataReader["user_mail"]);

					if(oDbDataReader["user_role"] != DBNull.Value)
						oUser.user_role = Convert.ToInt32(oDbDataReader["user_role"]);

					if(oDbDataReader["user_lvl_notif"] != DBNull.Value)
						oUser.user_lvl_notif = Convert.ToInt32(oDbDataReader["user_lvl_notif"]);

					if(oDbDataReader["user_pwd"] != DBNull.Value)
						oUser.user_pwd = Convert.ToString(oDbDataReader["user_pwd"]);

					if(oDbDataReader["user_login"] != DBNull.Value)
						oUser.user_login = Convert.ToString(oDbDataReader["user_login"]);
				}
				oDbDataReader.Close();
				return oUser;
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
	}
}
